﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using System.Data;
using ESOL.Common.Entity;
using ESOL.Business.Mst;
using Suzsoft.Smart.Data;
using AutoMapper;

namespace ESOL.Services
{
    [ServiceBehavior(UseSynchronizationContext = false,
    ConcurrencyMode = ConcurrencyMode.Multiple,
    InstanceContextMode = InstanceContextMode.PerCall)]
    public class MstService : IMstService
    {
        public bool TestConnect()
        {
            return true;
        }

        public DataSet GetMstCItemByType(string itemType)
        {
            try
            {
                return MstCommonItemBs.GetMstCItemByType(itemType);
            }
            catch (Exception ex)
            {
                throw new FaultException<ProcessExecutionFault>
                    (new ProcessExecutionFault(), ex.Message);
            }
        }

        public List<MstCommonItemEntity> GetMstCItemListByType(string itemType)
        {
            return MstCommonItemBs.GetMstCItemList(new MstCommonItemEntity { ItemType = itemType });
        }

        public MstProductEntity GetProductByKey(string id)
        {
            try
            {
                return MstProductBs.GetMstProduct(new MstProductEntity { ProductId = id });
            }
            catch (Exception ex)
            {
                throw new FaultException<ProcessExecutionFault>
                    (new ProcessExecutionFault(), ex.Message);
            }
        }

        /// <summary>
        /// 获取大类
        /// </summary>
        /// <param name="BroadClassName"></param>
        /// <param name="BroadClassCode"></param>
        /// <returns></returns>
        public DataSet GetBroadClass(string BroadClassName, string BroadClassCode)
        {
            string sql = @"select  PRODUCT_TYPE_CODE as  大类编码,PRODUCT_TYPE_NAME as 大类名称 from dbo.MST_PRODUCT_TYPE where PARENT_TYPE_ID is null and 1=1 ";
            if (!BroadClassCode.Equals(""))
            {
                sql += " and PRODUCT_TYPE_CODE like '%" + BroadClassCode + "%'";
            }
            if (!BroadClassName.Equals(""))
            {
                sql += " and PRODUCT_TYPE_NAME like '%" + BroadClassName + "%'";
            }
            sql += " order by PRODUCT_TYPE_CODE";
            return DataAccess.SelectDataSet(sql);
        }

        /// <summary>
        /// 获取中类
        /// </summary>
        /// <param name="BroadClassName"></param>
        /// <param name="BroadClassCode"></param>
        /// <returns></returns>
        public DataSet GetMiddleClass(List<string> ListIn)
        {
            string sql = @"select  PRODUCT_TYPE_CODE as  大类编码,PRODUCT_TYPE_NAME as 大类名称 from dbo.MST_PRODUCT_TYPE where  1=1  and PARENT_TYPE_ID IN 
                         (select PRODUCT_TYPE_ID from dbo.MST_PRODUCT_TYPE where PRODUCT_TYPE_CODE='" + ListIn[1] + "')";
            if (!ListIn[2].Equals(""))
            {
                sql += " and PRODUCT_TYPE_NAME like '%" + ListIn[2] + "%'";
            }
            if (!ListIn[3].Equals(""))
            {
                sql += " and PRODUCT_TYPE_CODE like '%" + ListIn[3] + "%'";
            }
            sql += " order by PRODUCT_TYPE_CODE";
            return DataAccess.SelectDataSet(sql);
        }

        /// <summary>
        /// 获取小类
        /// </summary>
        /// <param name="BroadClassName"></param>
        /// <param name="BroadClassCode"></param>
        /// <returns></returns>
        public DataSet GetType(List<string> ListIn)
        {
            string sql = @"select distinct TYPE_CODE as 类型编码,TYPE_NAME as 类型名称 from dbo.MST_PRODUCT where 1=1 and DELETE_FLAG=0  
                         and PRODUCT_TYPE_ID IN
                         (select PRODUCT_TYPE_ID from dbo.MST_PRODUCT_TYPE where PRODUCT_TYPE_CODE='" + ListIn[3] + "' and parent_type_id IN (select PRODUCT_TYPE_ID from dbo.MST_PRODUCT_TYPE where PRODUCT_TYPE_CODE='" + ListIn[1] + "' ))";
            if (!ListIn[4].Equals(""))
            {
                sql += " and TYPE_NAME like '%" + ListIn[4] + "%'";
            }
            if (!ListIn[5].Equals(""))
            {
                sql += " and TYPE_CODE like '%" + ListIn[5] + "%'";
            }
            return DataAccess.SelectDataSet(sql);
        }

        public DataSet GetStd(List<string> ListIn)
        {
            string sql = @"select distinct STD_CODE as 规格编码,STD_NAME as 规格名称 from dbo.MST_PRODUCT where 1=1 and DELETE_FLAG=0  
                         and PRODUCT_TYPE_ID IN
                         (select PRODUCT_TYPE_ID from dbo.MST_PRODUCT_TYPE where PRODUCT_TYPE_CODE='" + ListIn[3] + "' and parent_type_id IN (select PRODUCT_TYPE_ID from dbo.MST_PRODUCT_TYPE where PRODUCT_TYPE_CODE='" + ListIn[1] + "' ))";
            if (!ListIn[4].Equals(""))
            {
                sql += " and TYPE_NAME like '%" + ListIn[4] + "%'";
            }
            if (!ListIn[5].Equals(""))
            {
                sql += " and TYPE_CODE like '%" + ListIn[5] + "%'";
            }
            if (!ListIn[6].Equals(""))
            {
                sql += " and STD_NAME like '%" + ListIn[6] + "%'";
            }
            if (!ListIn[7].Equals(""))
            {
                sql += " and STD_CODE like '%" + ListIn[7] + "%'";
            }
            return DataAccess.SelectDataSet(sql);
        }

        public DataSet GetMaterial(List<string> ListIn)
        {
            string sql = @"select distinct MATERIAL_CODE as 材质编码,MATERIAL as 材质名称 from dbo.MST_PRODUCT where 1=1 and DELETE_FLAG=0  
                         and PRODUCT_TYPE_ID IN 
                         (select PRODUCT_TYPE_ID from dbo.MST_PRODUCT_TYPE where PRODUCT_TYPE_CODE='" + ListIn[3] + "' and parent_type_id IN (select PRODUCT_TYPE_ID from dbo.MST_PRODUCT_TYPE where PRODUCT_TYPE_CODE='" + ListIn[1] + "' ))";
            if (!ListIn[4].Equals(""))
            {
                sql += " and TYPE_NAME like '%" + ListIn[4] + "%'";
            }
            if (!ListIn[5].Equals(""))
            {
                sql += " and TYPE_CODE like '%" + ListIn[5] + "%'";
            }
            if (!ListIn[6].Equals(""))
            {
                sql += " and STD_NAME like '%" + ListIn[6] + "%'";
            }
            if (!ListIn[7].Equals(""))
            {
                sql += " and STD_CODE like '%" + ListIn[7] + "%'";
            }
            if (!ListIn[8].Equals(""))
            {
                sql += " and MATERIAL like '%" + ListIn[8] + "%'";
            }
            if (!ListIn[9].Equals(""))
            {
                sql += " and MATERIAL_CODE like '%" + ListIn[9] + "%'";
            }
            return DataAccess.SelectDataSet(sql);
        }

        public DataSet GetProduct(List<string> ListIn)
        {
            string sql = @"select distinct PRODUCT_CODE as 产品编码,PRODUCT_NAME as 产品名称 from dbo.MST_PRODUCT where 1=1 and DELETE_FLAG=0  
                         and PRODUCT_TYPE_ID IN 
                         (select PRODUCT_TYPE_ID from dbo.MST_PRODUCT_TYPE where PRODUCT_TYPE_CODE='" + ListIn[3] + "' and parent_type_id IN (select PRODUCT_TYPE_ID from dbo.MST_PRODUCT_TYPE where PRODUCT_TYPE_CODE='" + ListIn[1] + "' ))";
            if (!ListIn[4].Equals(""))
            {
                sql += " and TYPE_NAME like '%" + ListIn[4] + "%'";
            }
            if (!ListIn[5].Equals(""))
            {
                sql += " and TYPE_CODE like '%" + ListIn[5] + "%'";
            }
            if (!ListIn[6].Equals(""))
            {
                sql += " and STD_NAME like '%" + ListIn[6] + "%'";
            }
            if (!ListIn[7].Equals(""))
            {
                sql += " and STD_CODE like '%" + ListIn[7] + "%'";
            }
            if (!ListIn[8].Equals(""))
            {
                sql += " and MATERIAL like '%" + ListIn[8] + "%'";
            }
            if (!ListIn[9].Equals(""))
            {
                sql += " and MATERIAL_CODE like '%" + ListIn[9] + "%'";
            }
            if (!ListIn[10].Equals(""))
            {
                sql += " and PRODUCT_NAME like '%" + ListIn[10] + "%'";
            }
            return DataAccess.SelectDataSet(sql);
        }

        public DataSet GetRemark(List<string> ListIn)
        {
            string sql = @"select C.PRODUCT_TYPE_NAME as 大类名称,C.PRODUCT_TYPE_CODE AS 大类编码, B.PRODUCT_TYPE_NAME as 中类名称,B.PRODUCT_TYPE_CODE AS 中类编码 
                            ,A.PRODUCT_NAME AS 产品名称 ,A.PRODUCT_CODE AS 产品编码,A.MATERIAL_CODE AS 材质编码,A.MATERIAL AS 材质名称,A.TYPE_NAME AS 类型名称,A.TYPE_CODE AS 类型编码,A.STD_NAME AS 规格名称,A.STD_CODE AS 规格编码,A.DESCRIPTION AS 备注
                            from dbo.MST_PRODUCT A 
                            left join dbo.MST_PRODUCT_TYPE B 
                            on A.PRODUCT_TYPE_ID =B.PRODUCT_TYPE_ID
                            LEFT JoiN dbo.MST_PRODUCT_TYPE C
                            ON B.PARENT_TYPE_ID=C.PRODUCT_TYPE_ID
                            where  1=1 and A.DELETE_FLAG=1 and A.PRODUCT_TYPE_ID IN 
                            (select PRODUCT_TYPE_ID from dbo.MST_PRODUCT_TYPE where PRODUCT_TYPE_CODE='" + ListIn[3] + "' and parent_type_id IN (select PRODUCT_TYPE_ID from dbo.MST_PRODUCT_TYPE where PRODUCT_TYPE_CODE='" + ListIn[1] + "' ))";
            if (!ListIn[4].Equals(""))
            {
                sql += " and A.TYPE_NAME like '%" + ListIn[4] + "%'";
            }
            if (!ListIn[5].Equals(""))
            {
                sql += " and A.TYPE_CODE like '%" + ListIn[5] + "%'";
            }
            if (!ListIn[6].Equals(""))
            {
                sql += " and A.STD_NAME like '%" + ListIn[6] + "%'";
            }
            if (!ListIn[7].Equals(""))
            {
                sql += " and A.STD_CODE like '%" + ListIn[7] + "%'";
            }
            if (!ListIn[8].Equals(""))
            {
                sql += " and A.MATERIAL like '%" + ListIn[8] + "%'";
            }
            if (!ListIn[9].Equals(""))
            {
                sql += " and A.MATERIAL_CODE like '%" + ListIn[9] + "%'";
            }
            if (!ListIn[10].Equals(""))
            {
                sql += " and A.PRODUCT_NAME like '%" + ListIn[10] + "%'";
            }
            if (!ListIn[11].Equals(""))
            {
                sql += " and A.DESCRIPTION like '%" + ListIn[11] + "%'";
            }
            return DataAccess.SelectDataSet(sql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="con"></param>
        /// <returns></returns>
        public MaterialInfoPageResult GetProductAllDsByCondition(ProductQueryCondition con)
        {
            if (con == null)
                return null;

            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.masterClassName))
            {
                condition += " AND C.PRODUCT_TYPE_NAME LIKE '%" + con.masterClassName + "%'";
            }
            if (!string.IsNullOrEmpty(con.masterClassCode))
            {
                condition += " AND C.PRODUCT_TYPE_CODE LIKE '%" + con.masterClassCode + "%'";
            }
            if (!string.IsNullOrEmpty(con.middleClassName))
            {
                condition += " AND B.PRODUCT_TYPE_NAME LIKE '%" + con.middleClassName + "%'";
            }
            if (!string.IsNullOrEmpty(con.middleClassCode))
            {
                condition += " AND B.PRODUCT_TYPE_CODE LIKE '%" + con.middleClassCode + "%'";
            }
            if (!string.IsNullOrEmpty(con.typeName))
            {
                condition += " AND A.TYPE_NAME LIKE '%" + con.typeName + "%'";
            }
            if (!string.IsNullOrEmpty(con.typeCode))
            {
                condition += " AND A.TYPE_CODE LIKE '%" + con.typeCode + "%'";
            }
            if (!string.IsNullOrEmpty(con.stdName))
            {
                condition += " AND A.STD_NAME LIKE '%" + con.stdName + "%'";
            }
            if (!string.IsNullOrEmpty(con.stdCode))
            {
                condition += " AND A.STD_CODE LIKE '%" + con.stdCode + "%'";
            }
            if (!string.IsNullOrEmpty(con.materialName))
            {
                condition += " AND A.MATERIAL LIKE '%" + con.materialName + "%'";
            }
            if (!string.IsNullOrEmpty(con.materialCode))
            {
                condition += " AND A.MATERIAL_CODE LIKE '%" + con.materialCode + "%'";
            }
            if (!string.IsNullOrEmpty(con.productName))
            {
                condition += " AND A.PRODUCT_NAME LIKE '%" + con.productName + "%'";
            }
            if (!string.IsNullOrEmpty(con.productCode))
            {
                condition += " AND A.PRODUCT_CODE LIKE '%" + con.productCode + "%'";
            }
            if (!string.IsNullOrEmpty(con.description))
            {
                condition += " AND A.DESCRIPTION LIKE '%" + con.description + "%'";
            }
            if (!string.IsNullOrEmpty(con.specIn))
            {
                condition += " AND A.PRODUCT_SPEC_IN LIKE '%" + con.specIn + "%'";
            }
            if (con.storeType != 0)
            {
                condition += " AND A.STOCK_TYPE=" + con.storeType;
            }
            DataSet ds = MstProductBs.GetAllDsByCondition(pageIndex, pageSize, condition);
            if (ds != null)
            {
                MaterialInfoPageResult result = new MaterialInfoPageResult();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    MstProductEntity material = new MstProductEntity(dr);
                    Mapper.CreateMap<MstProductEntity, MaterialInfo>();
                    var materialDto = AutoMapper.Mapper.Map<MstProductEntity, MaterialInfo>(material);
                    if (dr["MAIN_TYPE_NAME"] != null)
                    {
                        materialDto.MainTypeName = dr["MAIN_TYPE_NAME"].ToString();
                    }
                    if (dr["MAIN_TYPE_CODE"] != null)
                    {
                        materialDto.MainTypeCode = dr["MAIN_TYPE_CODE"].ToString();
                    }
                    if (dr["MID_TYPE_NAME"] != null)
                    {
                        materialDto.MidTypeName = dr["MID_TYPE_NAME"].ToString();
                    }
                    if (dr["MID_TYPE_CODE"] != null)
                    {
                        materialDto.MidTypeCode = dr["MID_TYPE_CODE"].ToString();
                    }
                    if (result.results == null)
                        result.results = new List<MaterialInfo>();
                    result.results.Add(materialDto);
                }
                result.TotalCount = Convert.ToInt32(ds.Tables[0].Rows[0]["cnt"]);
                return result;
            }
            else return null;
        }

        //物料
        public bool IsExistProduct(string productCode)
        {
            if (string.IsNullOrEmpty(productCode))
                return false;
            var product = DataAccess.SelectSingle<MstProductEntity>(new MstProductEntity { ProductCode = productCode });
            if (product == null)
                return false;
            else return true;
        }

        public List<MstProductEntity> GetMstMaterialAllListByTypeId(string typeId)
        {
            if (string.IsNullOrEmpty(typeId))
                return null;
            return DataAccess.Select<MstProductEntity>(new MstProductEntity { ProductTypeId = typeId });
        }

        public bool InsertMstMaterial(MstProductEntity material)
        {
            if (material == null)
                return false;
            if (material.Photo == null)
            {
                material.Photo = new byte[] { };
            }
            return DataAccess.Insert(material);
        }

        public bool UpdateMstMaterial(MstProductEntity material)
        {
            if (material == null)
                return false;
            if (material.Photo == null)
            {
                material.Photo = new byte[] { };
            }
            return DataAccess.Update(material);
        }

        public bool UpdateMstMaterialDto(MaterialInfo materialDto)
        {
            if (materialDto == null)
                return false;
            Mapper.CreateMap<MaterialInfo, MstProductEntity>();
            var material = AutoMapper.Mapper.Map<MaterialInfo, MstProductEntity>(materialDto);
            if (material == null)
                return false;
            else return DataAccess.Update(material);
        }

        public bool DelMstMaterial(MstProductEntity material)
        {
            if (material == null)
                return false;
            return DataAccess.Delete(material);
        }

        public bool DelMstMaterialDto(MaterialInfo materialDto)
        {
            if (materialDto == null)
                return false;
            Mapper.CreateMap<MaterialInfo, MstProductEntity>();
            var material = AutoMapper.Mapper.Map<MaterialInfo, MstProductEntity>(materialDto);
            if (material == null)
                return false;
            else return DataAccess.Delete(material);
        }

        //CodeRule
        public DataSet GetCodeRuleAllDsByCondition(CodeRuleQueryCondition con)
        {
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.type))
            {
                condition += " and rul.CODE_TYPE='" + con.type + "'";
            }
            if (con.name != null)
            {
                condition += " and rul.CODE_NAME='" + con.name + "'";
            }
            if (con.prefix != null)
            {
                condition += " and rul.CODE_PREFIX='" + con.prefix + "'";
            }

            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from MST_CODE_RULE) as cnt
from MST_CODE_RULE
where CODE_TYPE not in (
    select top ({1}*({2}-1)) CODE_TYPE from MST_CODE_RULE)";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select rul.* from ({0}) rul";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.CODE_TYPE not in (
    select top ({1}*({2}-1)) CODE_TYPE from ({3}) t)";
                string sqlSource = @"select rul.* from MST_CODE_RULE rul where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            return ds;
        }

        public List<MstCodeRuleEntity> GetCodeRuleAllList()
        {
            return DataAccess.SelectAll<MstCodeRuleEntity>();
        }

        public bool AddCodeRule(MstCodeRuleEntity codeRule)
        {
            if (codeRule == null)
                return false;
            return DataAccess.Insert(codeRule);
        }

        public bool UpdateCodeRule(MstCodeRuleEntity codeRule)
        {
            if (codeRule == null)
                return false;
            return DataAccess.Update(codeRule);

        }

        public bool SaveCodeRule(CodeRuleSaveParam param)
        {
            if (param == null)
                return false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    if (param.dels != null)
                    {
                        DataAccess.Delete(param.dels, broker);
                    }
                    if (param.updates != null)
                    {
                        DataAccess.Update(param.updates, broker);
                    }
                    if (param.adds != null)
                    {
                        DataAccess.Insert(param.adds, broker);
                    }
                    broker.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return false;
                }
            }
        }

        //CommonItem
        public DataSet GetCommonItemAllDsByCondition(CommonItemQueryCondition con)
        {
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (con.code != null)
            {
                condition += " and item.ITEM_CODE='" + con.code + "'";
            }
            if (!string.IsNullOrEmpty(con.name))
            {
                condition += " and item.ITEM_NAME='" + con.name + "'";
            }
            if (con.type != null)
            {
                condition += " and item.ITEM_TYPE='" + con.type + "'";
            }
            if (con.value != null)
            {
                condition += " and item.ITEM_VALUE='" + con.value + "'";
            }
            condition += " and item.IS_DELETED='" + con.delFlag + "'";

            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from MST_COMMON_ITEM) as cnt
from MST_COMMON_ITEM
where ITEM_CODE not in (
    select top ({1}*({2}-1)) ITEM_CODE from MST_COMMON_ITEM)";
                sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                sql = @"select item.* from ({0}) item";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.ITEM_CODE not in (
    select top ({1}*({2}-1)) ITEM_CODE from ({3}) t)";
                string sqlSource = @"select item.* from MST_COMMON_ITEM item where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            }
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            return ds;
        }

        public List<MstCommonItemEntity> GetCommonItemAllList()
        {
            return DataAccess.SelectAll<MstCommonItemEntity>();
        }

        public List<MstCommonItemEntity> GetCommonItemAllListByType(string type)
        {
            if (string.IsNullOrEmpty(type))
                return null;
            return DataAccess.Select<MstCommonItemEntity>(new MstCommonItemEntity { ItemType = type });
        }

        public bool AddCommonItem(MstCommonItemEntity cmItem)
        {
            if (cmItem == null)
                return false;
            return DataAccess.Insert(cmItem);
        }

        public bool UpdateCommonItem(MstCommonItemEntity cmItem)
        {
            if (cmItem == null)
                return false;
            return DataAccess.Update(cmItem);
        }

        //客户
        public bool IsExistCustomer(string customerNo)
        {
            var tmp = DataAccess.SelectSingle<MstCustomerEntity>(new MstCustomerEntity { CustomerCode = customerNo });
            if (tmp == null)
                return false;
            else return true;
        }

        public bool UpdateCustomer(MstCustomerEntity customer)
        {
            if (customer == null)
                return false;
            customer.UpdatedDate = DateTime.Now;
            return DataAccess.Update(customer);
        }

        public bool SaveCustomer(CustomerSaveParam param)
        {
            if (param == null)
                return false;
            if (param.customer == null)
                return false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    MstCustomerEntity cur = DataAccess.SelectSingle<MstCustomerEntity>(new MstCustomerEntity { Id = param.customer.Id });
                    if (cur == null)//新增
                    {
                        param.customer.CreatedBy = param.userNo;
                        param.customer.UpdatedBy = param.userNo;
                        param.customer.CreatedDate = DateTime.Now;
                        param.customer.UpdatedDate = DateTime.Now;
                        param.customer.IsActive = 1;
                        if (string.IsNullOrEmpty(param.customer.CustomerCode))
                        {
                            param.customer.CustomerCode = MstCodeRuleBs.GetCurrentOrderCode("CustomerNo");
                        }
                        foreach (var contact in param.contacts)
                        {
                            contact.CustomerId = param.customer.Id;
                        }
                        foreach (var salesman in param.salesmans)
                        {
                            salesman.CustomerId = param.customer.Id;
                        }
                        broker.BeginTransaction();
                        DataAccess.Insert(param.customer, broker);
                        if (param.salesmans != null)
                        {
                            DataAccess.Insert(param.salesmans, broker);
                        }
                        if (param.contacts != null)
                        {
                            DataAccess.Insert(param.contacts, broker);
                        }
                        broker.Commit();
                        return true;
                    }
                    else//修改
                    {
                        param.customer.UpdatedBy = param.userNo;
                        param.customer.UpdatedDate = DateTime.Now;
                        foreach (var contact in param.contacts)
                        {
                            contact.CustomerId = param.customer.Id;
                        }
                        foreach (var salesman in param.salesmans)
                        {
                            salesman.CustomerId = param.customer.Id;
                        }
                        List<MstCustomerContactEntity> contactDels = DataAccess.Select<MstCustomerContactEntity>(new MstCustomerContactEntity { CustomerId = param.customer.Id });
                        List<MstCustomerSalesmanEntity> salesmanDels = DataAccess.Select<MstCustomerSalesmanEntity>(new MstCustomerSalesmanEntity { CustomerId = param.customer.Id });
                        broker.BeginTransaction();
                        if (contactDels != null)
                        {
                            DataAccess.Delete(contactDels, broker);
                        }
                        if (salesmanDels != null)
                        {
                            DataAccess.Delete(salesmanDels, broker);
                        }
                        DataAccess.Update(param.customer, broker);
                        if (param.salesmans != null)
                        {
                            DataAccess.Insert(param.salesmans, broker);
                        }
                        if (param.contacts != null)
                        {
                            DataAccess.Insert(param.contacts, broker);
                        }
                        broker.Commit();
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return false;
                }
            }
        }

        public bool DelCustomer(MstCustomerEntity customer)
        {
            if (customer == null)
                return false;
            var salesmen = DataAccess.Select<MstCustomerSalesmanEntity>(new MstCustomerSalesmanEntity { CustomerId = customer.Id });
            var contacts = DataAccess.Select<MstCustomerContactEntity>(new MstCustomerContactEntity { CustomerId = customer.Id });

            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    if (salesmen != null)
                    {
                        DataAccess.Delete(salesmen, broker);
                    }
                    if (contacts != null)
                    {
                        DataAccess.Delete(contacts, broker);
                    }
                    DataAccess.Delete(customer, broker);
                    broker.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return false;
                }
            }
        }

        public MstCustomerEntity GetCustomerByKey(string id)
        {
            try
            {
                return MstCustomerBs.GetMstCustomer(new MstCustomerEntity { Id = id });
            }
            catch (Exception ex)
            {
                throw new FaultException<ProcessExecutionFault>
                    (new ProcessExecutionFault(), ex.Message);
            }
        }

        public CustomerPageResult GetCustomerAllListByCondition(CustomerQueryCondition con)
        {
            try
            {
                int pageIndex = 1;
                int pageSize = 50;
                if (con.txtPageIndex != 0)
                    pageIndex = con.txtPageIndex;
                if (con.txtPageSize != 0)
                    pageSize = con.txtPageSize;
                string condition = "";
                condition += " and cu.IS_ACTIVE=" + con.IsActive;
                if (!string.IsNullOrEmpty(con.customerNo))
                {
                    condition += " and cu.CUSTOMER_CODE='" + con.customerNo + "'";
                }
                if (!string.IsNullOrEmpty(con.customerName))
                {
                    condition += " and cu.CUSTOMER_NAME='" + con.customerName + "'";
                }
                if (con.status != null)
                {
                    condition += " and cu.STATUS='" + con.status + "'";
                }
                string sql = "";
                if (string.IsNullOrEmpty(condition))
                {
                    string sqlSource = @"select top {0} *,(select count(1) from MST_CUSTOMER) as cnt
from MST_CUSTOMER
where id not in (
    select top ({1}*({2}-1)) id from MST_CUSTOMER order by created_date desc
) order by created_date desc";
                    sqlSource = string.Format(sqlSource, pageSize, pageSize, pageIndex);
                    sql = @"select cu.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from ({0}) cu
left join BPMDB.dbo.PW_User u1 on cu.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on cu.CREATED_BY=u2.userNo";
                    sql = string.Format(sql, sqlSource);
                }
                else
                {
                    sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t order by created_date desc
) order by created_date desc";
                    string sqlSource = @"select cu.*,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER 
from MST_CUSTOMER cu
left join BPMDB.dbo.PW_User u1 on cu.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on cu.CREATED_BY=u2.userNo
where 1=1 ";
                    sqlSource += condition;
                    sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
                }
                DataSet ds = DataAccess.SelectDataSet(sql);
                if (ds == null)
                    return null;
                CustomerPageResult result = new CustomerPageResult();
                if (ds.Tables[0].Rows.Count == 0)
                {
                    result.results = null;
                    result.TotalCount = 0;
                    return result;
                }
                List<MstCustomerEntity> results = new List<MstCustomerEntity>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    MstCustomerEntity cu = new MstCustomerEntity(dr);
                    results.Add(cu);
                }
                result.results = results;
                result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
                return result;
            }
            catch (Exception ex)
            {
                throw new FaultException<ProcessExecutionFault>
                    (new ProcessExecutionFault(), ex.Message);
            }
        }

        //客户联系人
        public List<MstCustomerContactEntity> GetCustomerContactAllListByCustomerId(string customerId)
        {
            if (string.IsNullOrEmpty(customerId))
                return null;
            return DataAccess.Select<MstCustomerContactEntity>(new MstCustomerContactEntity { CustomerId = customerId });

        }

        public List<MstCustomerContactEntity> GetCustomerContactAllListByCondition(CustomerContactQueryCondition con)
        {
            if (con == null)
                return null;
            string sql = @"select ctc.* 
from MST_CUSTOMER_CONTACT ctc
left join MST_CUSTOMER cst on ctc.CUSTOMER_ID=cst.ID where 1=1";
            if (con.CustomerId != null)
            {
                sql += " and ctc.CUSTOMER_ID='" + con.CustomerId + "'";
            }
            if (con.CustomerName != null)
            {
                sql += " and cst.CUSTOMER_NAME like '%" + con.CustomerName + "%'";
            }
            if (con.ContactName != null)
            {
                sql += " and ctc.CONTACT_NAME like '%" + con.ContactName + "%'";
            }
            if (con.IsActive == 1)
            {
                sql += " and ctc.IS_ACTIVE=1";
            }
            else
            {
                sql += " and ctc.IS_ACTIVE=0";
            }
            return DataAccess.Select<MstCustomerContactEntity>(sql);
        }

        public bool DelCustomerContact(MstCustomerContactEntity contact)
        {
            if (contact == null)
                return false;
            return DataAccess.Delete(contact);
        }

        //客户业务员
        public List<MstCustomerSalesmanEntity> GetCustomerSalesmanAllListByCustomerId(string customerId)
        {
            if (string.IsNullOrEmpty(customerId))
                return null;
            return DataAccess.Select<MstCustomerSalesmanEntity>(new MstCustomerSalesmanEntity { CustomerId = customerId });

        }

        public bool DelCustomerSalesman(MstCustomerSalesmanEntity salesman)
        {
            if (salesman == null)
                return false;
            return DataAccess.Delete(salesman);
        }

        //Area 省市
        public List<MstAreaEntity> GetAllProvince()
        {
            return DataAccess.Select<MstAreaEntity>(new MstAreaEntity { AreaType = "1" });
        }

        public List<MstAreaEntity> GetAreaAllListByCondition(AreaQueryCondition param)
        {
            if (param == null)
                return null;
            string sql = "select * from MST_AREA a where 1=1";
            if (!string.IsNullOrEmpty(param.AreaName))
            {
                sql += " and a.AREA_NAME='" + param.AreaName + "'";
            }
            if (!string.IsNullOrEmpty(param.AreaType))
            {
                sql += " and a.AREA_TYPE='" + param.AreaType + "'";
            }
            if (!string.IsNullOrEmpty(param.ParentAreaId))
            {
                sql += " and a.PARENT_AREA_ID='" + param.ParentAreaId + "'";
            }
            return DataAccess.Select<MstAreaEntity>(sql);

        }

        //生产范围
        public MstIndustryTypeEntity GetMstIndustryTypeByKey(string typeCode)
        {
            if (string.IsNullOrEmpty(typeCode))
                return null;
            return DataAccess.SelectSingle<MstIndustryTypeEntity>(new MstIndustryTypeEntity { IndustryTypeCode = typeCode });
        }

        //产品大类
        public MstProductTypeEntity GetMstProductTypeByKey(string typeId)
        {
            if (string.IsNullOrEmpty(typeId))
                return null;
            return DataAccess.SelectSingle<MstProductTypeEntity>(new MstProductTypeEntity { ProductTypeId = typeId });

        }

        //查询产品父类型
        public List<MstProductTypeEntity> GetMstProductParentType()
        {
            return DataAccess.Select<MstProductTypeEntity>(new MstProductTypeEntity { Category = "CG01" });
        }

        public List<MaterialTypeResult> GetMstMaterialTypeAllListByConditon(MaterialTypeQueryCondition param)
        {
            if (param == null)
                return null;
            string sql = @"select a.* from MST_PRODUCT_TYPE a
left join MST_PRODUCT_TYPE p on a.PARENT_TYPE_ID=p.PRODUCT_TYPE_ID 
where 1=1";
            if (!string.IsNullOrEmpty(param.ParentTypeId))
            {
                sql += " and a.PARENT_TYPE_ID='" + param.ParentTypeId + "'";
            }
            if (!string.IsNullOrEmpty(param.TypeName))
            {
                sql += " and a.PRODUCT_TYPE_NAME='" + param.TypeName + "'";
            }
            if (!string.IsNullOrEmpty(param.TypeCode))
            {
                sql += " and a.PRODUCT_TYPE_CODE='" + param.TypeCode + "'";
            }
            if (!string.IsNullOrEmpty(param.Category))
            {
                sql += " and a.CATEGORY='" + param.Category + "'";
            }
            List<MstProductTypeEntity> types = DataAccess.Select<MstProductTypeEntity>(sql);
            List<MaterialTypeResult> result = new List<MaterialTypeResult>();
            if (types != null)
            {
                foreach (var type in types)
                {
                    Mapper.CreateMap<MstProductTypeEntity, MaterialTypeResult>().ForMember(d => d.SelectedFlag, s => s.Ignore());
                    var typeDto = AutoMapper.Mapper.Map<MstProductTypeEntity, MaterialTypeResult>(type);
                    result.Add(typeDto);
                }

            }
            return result;
        }

        public bool SaveMstProductType(MstProductTypeEntity productType)
        {
            if (productType == null)
                return false;
            MstProductTypeEntity type = DataAccess.SelectSingle<MstProductTypeEntity>(new MstProductTypeEntity { ProductTypeId = productType.ProductTypeId });
            if (type == null)
            {
                return DataAccess.Insert(productType);
            }
            else
            {
                return DataAccess.Update(productType);
            }

        }

        public bool DelMstProductType(MstProductTypeEntity productType)
        {
            if (productType == null)
                return false;
            return DataAccess.Delete(productType);

        }

        //根据物料ID，查询物料及类型信息
        public MaterialInfo GetMstMaterialInfoByKey(string id)
        {
            if (string.IsNullOrEmpty(id))
                return null;
            var material = DataAccess.SelectSingle<MstProductEntity>(new MstProductEntity { ProductId = id });
            if (material != null)
            {
                Mapper.CreateMap<MstProductEntity, MaterialInfo>();
                var result = AutoMapper.Mapper.Map<MstProductEntity, MaterialInfo>(material);
                var materialType = DataAccess.SelectSingle<MstProductTypeEntity>(new MstProductTypeEntity { ProductTypeId = material.ProductTypeId });
                if (materialType != null)
                {
                    result.MidTypeName = materialType.ProductTypeName;
                    result.MidTypeCode = materialType.ProductTypeName;
                    var materialParentType = DataAccess.SelectSingle<MstProductTypeEntity>(new MstProductTypeEntity { ProductTypeId = materialType.ParentTypeId });
                    if (materialType != null)
                    {
                        result.MainTypeName = materialType.ProductTypeName;
                        result.MainTypeCode = materialType.ProductTypeCode;
                    }
                }
                return result;
            }
            else return null;
        }

        //供应商产品范围
        public List<SupplierProductScopeDto> GetSupplierScopeDtoAllList()
        {
            var scopes = DataAccess.Select<MstCommonItemEntity>(new MstCommonItemEntity { ItemType = "SSCOPE", IsDeleted = 0 });
            if (scopes == null)
                return null;
            List<SupplierProductScopeDto> dtos = new List<SupplierProductScopeDto>();
            foreach (var scope in scopes)
            {
                Mapper.CreateMap<MstCommonItemEntity, SupplierProductScopeDto>().ForMember(d => d.SelectedFlag, s => s.Ignore());
                var dto = AutoMapper.Mapper.Map<MstCommonItemEntity, SupplierProductScopeDto>(scope);
                dtos.Add(dto);
            }
            return dtos;
        }


        //物料保存
        public bool SaveMstMaterial(MaterialSaveParam param)
        {
            if (param == null)
                return false;
            if (param.MaterialDto == null)
                return false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    MstProductEntity cur = DataAccess.SelectSingle<MstProductEntity>(new MstProductEntity { ProductId = param.MaterialDto.ProductId });
                    Mapper.CreateMap<MaterialInfo, MstProductEntity>();
                    var material = AutoMapper.Mapper.Map<MaterialInfo, MstProductEntity>(param.MaterialDto);
                    if (cur == null)//新增
                    {
                        List<MstProductPriceEntity> adds = new List<MstProductPriceEntity>();
                        foreach (var dto in param.addsPriceDto)
                        {
                            Mapper.CreateMap<MaterialPriceDto, MstProductPriceEntity>();
                            var price = AutoMapper.Mapper.Map<MaterialPriceDto, MstProductPriceEntity>(dto);
                            price.CreatedBy = param.UserNo;
                            price.CreatedDate = DateTime.Now;
                            price.UpdatedBy = param.UserNo;
                            price.UpdatedDate = DateTime.Now;
                            adds.Add(price);
                        }
                        broker.BeginTransaction();
                        DataAccess.Insert(material, broker);
                        DataAccess.Insert(adds, broker);
                        broker.Commit();
                        return true;
                    }
                    else//修改
                    {
                        List<MstProductPriceEntity> adds = new List<MstProductPriceEntity>();
                        List<MstProductPriceEntity> updates = new List<MstProductPriceEntity>();
                        List<MstProductPriceEntity> dels = new List<MstProductPriceEntity>();

                        foreach (var dto in param.addsPriceDto)
                        {
                            Mapper.CreateMap<MaterialPriceDto, MstProductPriceEntity>();
                            var price = AutoMapper.Mapper.Map<MaterialPriceDto, MstProductPriceEntity>(dto);
                            price.CreatedBy = param.UserNo;
                            price.CreatedDate = DateTime.Now;
                            price.UpdatedBy = param.UserNo;
                            price.UpdatedDate = DateTime.Now;
                            adds.Add(price);
                        }
                        foreach (var dto in param.updatesPriceDto)
                        {
                            Mapper.CreateMap<MaterialPriceDto, MstProductPriceEntity>();
                            var price = AutoMapper.Mapper.Map<MaterialPriceDto, MstProductPriceEntity>(dto);
                            price.UpdatedBy = param.UserNo;
                            price.UpdatedDate = DateTime.Now;
                            updates.Add(price);
                        }
                        foreach (var dto in param.delsPriceDto)
                        {
                            Mapper.CreateMap<MaterialPriceDto, MstProductPriceEntity>();
                            var price = AutoMapper.Mapper.Map<MaterialPriceDto, MstProductPriceEntity>(dto);
                            dels.Add(price);
                        }
                        broker.BeginTransaction();
                        DataAccess.Update(material, broker);
                        DataAccess.Delete(dels, broker);
                        DataAccess.Update(updates, broker);
                        DataAccess.Insert(adds, broker);
                        broker.Commit();
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return false;
                }
            }

        }


        //查询物料历史价格
        public List<MaterialPriceDto> GetMstMaterialPriceDtoAllListByMaterialId(string materialId)
        {
            if (string.IsNullOrEmpty(materialId))
                return null;
            var prices = (List<MstProductPriceEntity>)DataAccess.Select<MstProductPriceEntity>(new MstProductPriceEntity { Id = materialId });
            if (prices == null)
                return null;
            List<MaterialPriceDto> dtos = new List<MaterialPriceDto>();
            foreach (var price in prices)
            {
                Mapper.CreateMap<MstProductPriceEntity, MaterialPriceDto>();
                var dto = AutoMapper.Mapper.Map<MstProductPriceEntity, MaterialPriceDto>(price);
                MstSupplierEntity supplier = null;
                if (!string.IsNullOrEmpty(price.SupplierId))
                {
                    supplier = DataAccess.SelectSingle<MstSupplierEntity>(new MstSupplierEntity { Id = price.SupplierId });
                    if (supplier != null)
                        dto.SupplierName = supplier.SupplierName;
                }
                string sql = @"select * from BPMDB.dbo.PW_User where userNo='{0}'";
                sql = string.Format(sql, price.CreatedBy);
                DataSet ds = DataAccess.SelectDataSet(sql);
                if (ds.Tables[0].Rows.Count == 1)
                {
                    dto.CreatedUser = ds.Tables[0].Rows[0]["firstName"].ToString();
                }
                sql = @"select * from BPMDB.dbo.PW_User where userNo='{0}'";
                sql = string.Format(sql, price.UpdatedBy);
                ds = DataAccess.SelectDataSet(sql);
                if (ds.Tables[0].Rows.Count == 1)
                {
                    dto.UpdatedUser = ds.Tables[0].Rows[0]["firstName"].ToString();
                }
                dtos.Add(dto);
            }

            return dtos;
        }


        //根据用户，查询其工作日历的任务
        public List<MstScheduleEntity> GetMstScheduleByConditon(MstScheduleQueryCondition param)
        {
            if (param == null)
                return null;
            string con = "";
            if (!string.IsNullOrEmpty(param.UserNo))
            {
                con += " and sch.USER_NO='" + param.UserNo + "'";
            }
            if (param.Type != 0)
            {
                con += " and sch.TYPE=" + param.Type;
            }
            if (param.StartDate != null)
            {
                con += " and sch.START_DATE>='" + param.StartDate + "'";
            }
            if (param.EndDate != null)
            {
                con += " and sch.START_DATE<='" + param.EndDate + "'";
            }
            string sql = @"select * from MST_SCHEDULE sch where 1=1 {0}";
            if (!string.IsNullOrEmpty(con))
            {
                sql = string.Format(sql, con);
            }
            return DataAccess.Select<MstScheduleEntity>(sql);


        }

        public bool SaveMstSchedule(MstScheduleSaveParam param)
        {
            if (param == null)
                return false;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    if (param.dels != null)
                        DataAccess.Delete(param.dels, broker);
                    if (param.updates != null)
                    {
                        foreach (var update in param.updates)
                        {
                            update.UpdatedBy = param.userNo;
                            update.UpdatedDate = DateTime.Now;
                        }
                        DataAccess.Update(param.updates, broker);
                    }
                    if (param.adds != null)
                    {
                        foreach (var add in param.adds)
                        {
                            add.CreatedBy = param.userNo;
                            add.CreatedDate = DateTime.Now;
                            add.UpdatedBy = param.userNo;
                            add.UpdatedDate = DateTime.Now;
                            add.UserNo = param.userNo;
                        }
                        DataAccess.Insert(param.adds, broker);
                    }
                    broker.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    return false;
                }
            }

        }

        public HmDomainsEntity GetHmDomain()
        {
            return DataAccess.SelectSingle<HmDomainsEntity>(new HmDomainsEntity { Domainactive = 1 });
        }

        public HmAccountsEntity GetHmAccountByAdd(string mailAdd)
        {
            if (string.IsNullOrEmpty(mailAdd))
                return null;
            return DataAccess.SelectSingle<HmAccountsEntity>(new HmAccountsEntity { Accountaddress = mailAdd });
        }

        public bool SaveHmMailUser(HmAccountsEntity account)
        {
            if (account == null)
                return false;
            var act = DataAccess.SelectSingle<HmAccountsEntity>(new HmAccountsEntity { Accountaddress = account.Accountaddress });
            if (act == null)
            {
                var domain = DataAccess.SelectSingle<HmDomainsEntity>(new HmDomainsEntity { Domainactive = 1 });
                if (domain == null)
                    return false;
                account.Accountdomainid = domain.Domainid;
                account.Accountpwencryption = 0;
                string sql = @"INSERT INTO hm_accounts (accountdomainid, accountaddress, accountpassword, 
accountactive, accountisad, accountaddomain, accountadusername, accountmaxsize, accountvacationmessageon, 
accountvacationmessage, accountvacationsubject, accountvacationexpires, accountvacationexpiredate, 
accountpwencryption, accountadminlevel, accountforwardenabled, accountforwardaddress, 
accountforwardkeeporiginal, accountenablesignature, accountsignatureplaintext, accountsignaturehtml, 
accountpersonfirstname, accountpersonlastname) 
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', 
'{19}', '{20}', '{21}','{22}')";
                sql = string.Format(sql, account.Accountdomainid, account.Accountaddress, account.Accountpassword,
                    account.Accountactive, account.Accountisad, account.Accountaddomain, account.Accountadusername,
                    account.Accountmaxsize, account.Accountvacationmessageon, account.Accountvacationmessage,
                    account.Accountvacationsubject, account.Accountvacationexpires, account.Accountvacationexpiredate,
                    account.Accountpwencryption, account.Accountadminlevel, account.Accountforwardenabled, account.Accountforwardaddress,
                    account.Accountforwardkeeporiginal, account.Accountenablesignature, account.Accountsignatureplaintext, account.Accountsignaturehtml,
                    account.Accountpersonfirstname, account.Accountpersonlastname
                    );
                using (DataAccessBroker broker = DataAccessFactory.Instance())
                {
                    try
                    {
                        broker.BeginTransaction();
                        broker.ExecuteSQL(sql);
                        broker.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        return false;
                    }
                }
            }
            else
            {
                return DataAccess.Update(account);
            }
        }

        public MstMailserverConfigEntity GetMstMailserverCfg()
        {
            return DataAccess.SelectSingle<MstMailserverConfigEntity>(new MstMailserverConfigEntity { IsActive = 1 });
        }

        //主物料查询
        public MstMaterialExDto GetMstMaterialExDtoByMaterialId(string materialId)
        {
            if (string.IsNullOrEmpty(materialId))
                return null;
            MstMaterialExEntity materialEx = DataAccess.SelectSingle<MstMaterialExEntity>(new MstMaterialExEntity { MaterialId = materialId });
            Mapper.CreateMap<MstMaterialExEntity, MstMaterialExDto>();
            var dto = AutoMapper.Mapper.Map<MstMaterialExEntity, MstMaterialExDto>(materialEx);
            return dto;

        }

        public MstMaterialExEntity GetMstMaterialExByMaterialId(string materialId)
        {
            if (string.IsNullOrEmpty(materialId))
                return null;
            return DataAccess.SelectSingle<MstMaterialExEntity>(new MstMaterialExEntity { MaterialId = materialId });
        }

        public MstMaterialExPageResult GetMstMaterialExAllListByCondition(MaterialExQueryCondition con)
        {
            if (con == null)
                return null;

            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;
            string condition = "";
            if (!string.IsNullOrEmpty(con.typeName))
            {
                condition += " AND prd.TYPE_NAME LIKE '%" + con.typeName + "%'";
            }
            if (!string.IsNullOrEmpty(con.typeCode))
            {
                condition += " AND prd.TYPE_CODE LIKE '%" + con.typeCode + "%'";
            }
            if (!string.IsNullOrEmpty(con.stdName))
            {
                condition += " AND prd.STD_NAME LIKE '%" + con.stdName + "%'";
            }
            if (!string.IsNullOrEmpty(con.stdCode))
            {
                condition += " AND prd.STD_CODE LIKE '%" + con.stdCode + "%'";
            }
            if (!string.IsNullOrEmpty(con.material))
            {
                condition += " AND prd.MATERIAL LIKE '%" + con.materialName + "%'";
            }
            if (!string.IsNullOrEmpty(con.materialName))
            {
                condition += " AND prd.PRODUCT_NAME LIKE '%" + con.materialName + "%'";
            }
            if (!string.IsNullOrEmpty(con.materialCode))
            {
                condition += " AND prd.PRODUCT_CODE LIKE '%" + con.materialCode + "%'";
            }
            if (con.storeType != 0)
            {
                condition += " AND prod.STOCK_TYPE=" + con.storeType;
            }
            if (con.source != 0)
            {
                condition += " AND mex.SOURCE=" + con.source;
            }
            if (con.minLeadTime != 0)
            {
                condition += " AND mex.LEAD_TIME>=" + con.minLeadTime;
            }
            if (con.minLeadTime != 0)
            {
                condition += " AND mex.LEAD_TIME<=" + con.maxLeadTime;
            }
            DataSet ds = MstProductBs.GetMaterialExAllDsByCondition(pageIndex, pageSize, condition);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                MstMaterialExPageResult result = new MstMaterialExPageResult();
                List<MstMaterialExDto> materialExDtos = new List<MstMaterialExDto>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    MstMaterialExEntity ex = new MstMaterialExEntity(dr);
                    Mapper.CreateMap<MstMaterialExEntity, MstMaterialExDto>();
                    MstMaterialExDto materialExDto = AutoMapper.Mapper.Map<MstMaterialExEntity, MstMaterialExDto>(ex);
                    if (dr["PRODUCT_NAME"] != DBNull.Value)
                    {
                        materialExDto.MaterialName = dr["PRODUCT_NAME"].ToString();
                    }
                    if (dr["TYPE_NAME"] != DBNull.Value)
                    {
                        materialExDto.TypeName = dr["TYPE_NAME"].ToString();
                    }
                    if (dr["STD_NAME"] != DBNull.Value)
                    {
                        materialExDto.StdName = dr["STD_NAME"].ToString();
                    }
                    if (dr["MATERIAL"] != DBNull.Value)
                    {
                        materialExDto.Material = dr["MATERIAL"].ToString();
                    }
                    if (dr["UNIT"] != DBNull.Value)
                    {
                        materialExDto.Unit = dr["UNIT"].ToString();
                    }
                    if (dr["MINOR_UNIT"] != DBNull.Value)
                    {
                        materialExDto.MinorUnit = dr["MINOR_UNIT"].ToString();
                    }
                    if (dr["SUGGEST_PRICE"] != DBNull.Value)
                    {
                        materialExDto.SuggestPrice = (decimal)dr["SUGGEST_PRICE"];
                    }

                    materialExDtos.Add(materialExDto);
                }
                result.materialEx = materialExDtos;
                result.TotalCount = (int)ds.Tables[0].Rows[0]["cnt"];
                return result;
            }
            else return null;
        }

        //主物料保存
        public bool SaveMstMaterialEx(MstMaterialExEntity materialEx)
        {
            if (materialEx == null)
                return false;
            MstMaterialExEntity ex = DataAccess.SelectSingle<MstMaterialExEntity>(new MstMaterialExEntity { MaterialId = materialEx.MaterialId });
            if (ex == null)
            {
                return DataAccess.Insert(materialEx);
            }
            else
            {
                return DataAccess.Update(materialEx);
            }

        }

        //主物料删除
        public bool DelMstMaterialExDto(MstMaterialExDto materialExDto)
        {
            if (materialExDto == null)
                return false;
            Mapper.CreateMap<MstMaterialExDto, MstMaterialExEntity>();
            var materialEx = AutoMapper.Mapper.Map<MstMaterialExDto, MstMaterialExEntity>(materialExDto);

            return DataAccess.Delete(materialEx);
        }

        public string CreateCodeRule(CodeRuleQueryCondition param)
        {
            try
            {
                string re = MstCodeRuleBs.GetCurrentOrderCode(param.type);
                return re;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }


        //获取最新单据编码
        public string GetCurrentOrderCode(string codeType)
        {
            if (string.IsNullOrEmpty(codeType))
            {
                return null;
            }
            MstCodeRuleEntity entity = MstCodeRuleBs.GetMstCodeRuleByCodeType(codeType);
            if (entity == null)
            {
                return null;
            }
            char paddingChar = Convert.ToChar("0");
            string codeDateFormat = "yyyyMMdd";
            if (string.IsNullOrEmpty(entity.CodeDateFormat) == false)
            {
                codeDateFormat = entity.CodeDateFormat;
            }
            if (entity.CurrentDay == DateTime.Now.ToString(codeDateFormat))
            {
                entity.CurrentValue++;
            }
            else
            {
                entity.CurrentDay = DateTime.Now.ToString(codeDateFormat);
                entity.CurrentValue = 1;
            }
            string currentValue = entity.CurrentValue.ToString().PadLeft(entity.CodeSeqLen, paddingChar);
            DataAccess.Update(entity);
            return entity.CodePrefix + DateTime.Now.ToString(codeDateFormat) + currentValue;
        }

        //签名档
        public MstSignDtoPageResult GetMstSignDtoPageResult(MstSignDtoQueryCondition con)
        {
            if (con == null)
                return null;
            int pageIndex = 1;
            int pageSize = 50;
            if (con.txtPageIndex != 0)
                pageIndex = con.txtPageIndex;
            if (con.txtPageSize != 0)
                pageSize = con.txtPageSize;

            string sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.id not in (
    select top ({1}*({2}-1)) id from ({3}) t)";
            string sqlSource = @"select s.*,u.firstName as CREATED_USER,u2.firstName as UPDATED_USER,u3.firstName as USER_NAME 
from MST_SIGN s
left join BPMDB.dbo.PW_User u on s.CREATED_BY=u.userNo
left join BPMDB.dbo.PW_User u2 on s.UPDATED_BY=u2.userNo
left join BPMDB.dbo.PW_User u3 on s.USER_NO=u3.userNo";
            sql = string.Format(sql, pageSize, pageSize, pageIndex, sqlSource);
            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds == null)
                return null;
            MstSignDtoPageResult result = new MstSignDtoPageResult();
            List<MstSignDto> dtos = new List<MstSignDto>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                MstSignEntity e = new MstSignEntity(dr);
                Mapper.CreateMap<MstSignEntity, MstSignDto>();
                var dto = Mapper.Map<MstSignEntity, MstSignDto>(e);
                if (dr["CREATED_USER"] != DBNull.Value)
                    dto.CreatedUser = dr["CREATED_USER"].ToString();
                if (dr["UPDATED_USER"] != DBNull.Value)
                    dto.UpdatedUser = dr["UPDATED_USER"].ToString();
                if (dr["USER_NAME"] != DBNull.Value)
                    dto.UserName = dr["USER_NAME"].ToString();
                dtos.Add(dto);
            }
            result.result = dtos;
            result.TotalCount = dtos.Count;
            return result;

        }

        public bool SaveMstSignDto(MstSignDto signDto)
        {
            var sign = MstSignBs.GetMstSign(new MstSignEntity { Id = signDto.Id });
            if (sign == null)
            {
                Mapper.CreateMap<MstSignDto, MstSignEntity>();
                var s = Mapper.Map<MstSignDto, MstSignEntity>(signDto);
                s.CreatedDate = DateTime.Now;
                s.UpdatedDate = DateTime.Now;
                return MstSignBs.InsertMstSign(s);
            }
            else
            {
                Mapper.CreateMap<MstSignDto, MstSignEntity>();
                var s = Mapper.Map<MstSignDto, MstSignEntity>(signDto);
                return MstSignBs.UpdateMstSign(s);
            }
        }

        public bool DelMstSignDto(MstSignDto signDto)
        {
            var sign = MstSignBs.GetMstSign(new MstSignEntity { Id = signDto.Id });
            return MstSignBs.DeleteMstSign(sign);
        }

        public MstSignEntity GetMstSignByUserNo(string userNo)
        {
            return MstSignBs.GetMstSign(new MstSignEntity { UserNo = userNo });
        }

    }
}
